import re

from typing import Dict, List, Optional, Union, Any
import pymysql
from pymysql.cursors import DictCursor

import settings
from utils.util_logger import logger


class DBUtil:
    """数据库查询工具: 使用sql的参数化查询,防止sql注入!"""

    def __init__(self, table: str = "users"):
        self.table = table
        try:
            self.connection = pymysql.connect(
                host=settings.MYSQL_HOST,
                port=settings.MYSQL_PORT,
                user=settings.MYSQL_USER,
                passwd=settings.MYSQL_PWD,
                database=settings.MYSQL_DB,
            )
            self.cursor = self.connection.cursor(DictCursor)
            logger.info(f"connect Success: host - {settings.MYSQL_HOST}")
        except Exception as e:
            logger.info(f"Fail: 连接数据库失败 - {str(e)}")

    def query_by_id(self, id: int):
        """根据id查询表"""
        sql = f"select * from {self.table} where id = %d;"
        try:
            self.cursor.execute(sql, (id,))
            logger.info(f"query_by_id Success: {sql}")
            return self.cursor.fetchone()
        except Exception as e:
            logger.info(f"Fail: query_by_id - {str(e)}")

    def query_by_username(self, username: str) -> Optional[dict]:
        """
        安全的用户名查询方法

        Args:
            username: 要查询的用户名（自动过滤特殊字符）
        Returns:
            用户数据字典（如果存在）或 None
        Raises:
            DatabaseError: 数据库操作失败
        """
        # 输入验证
        if not isinstance(username, str) or not username.strip():
            raise ValueError("用户名必须为非空字符串")

        sql = f"SELECT * FROM {self.table} WHERE username = %s"
        try:
            self.cursor.execute(sql, (username.strip(),))
            result = self.cursor.fetchone()

            if result:
                # 转换为字典（如果使用DictCursor可省略）
                columns = [col[0] for col in self.cursor.description]
                return dict(zip(columns, result))
            return None

        except Exception as e:
            logger.error(f"查询用户失败: {str(e)}", exc_info=True)
            raise  # 向上抛出异常

    def delete_by_username(self, username: str) -> bool:
        """根据username删除用户信息"""
        sql = f"delete from {self.table} where username = %s ;"
        try:
            self.cursor.execute(sql, (username,))
            self.connection.commit()
            logger.info(f"delete_by_username Success: {sql}")
            return True
        except Exception as e:
            logger.info(f"Fail: delete_by_username - {username} - {str(e)}")
            return False

    def query_all(self) -> List[Dict[str, Union[Any, None]]]:
        sql = f"select * from {self.table};"
        try:
            self.cursor.execute(sql)
            logger.info(f"query_all Success: {sql}")
            return self.cursor.fetchall()
        except Exception as e:
            logger.info(f"Fail: query_all - {str(e)}")

    def insert_sql(self, data: dict) -> bool:
        """创建数据"""
        # 判断data是否是字典格式
        if not isinstance(data, Dict):
            raise ValueError(f"Error: create_sql - {data} is not type of Dict!")

        try:
            # 构建参数化SQL
            columns = ",".join(data.keys())
            # 替换password为hashed_password
            new_cols = re.sub("password", "hashed_password", columns)

            placeholders = ", ".join(["%s"] * len(data))

            sql = f"insert into {self.table} ({new_cols}) values({placeholders});"
            logger.info(sql)
            self.cursor.execute(sql, tuple(data.values()))
            self.connection.commit()
            logger.info(f"Insert Success: {sql}")
            return True
        except Exception as e:
            # 插入异常则回滚
            self.roll_back()
            logger.error(f"Insert Fail: {str(e)}, SQL: {sql}, 数据: {data}")
            return False

    def roll_back(self):
        """数据库回滚"""
        self.connection.rollback()

    def close(self):
        """关闭数据库连接"""
        self.cursor.close()
        self.connection.close()
